package com.stephen.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.util.Date;

/**
 * Created by stephen on 2021-03-08 21:51 .
 * Description: Excel读取
 *  注意获取不同类型的值
 */
public class ExcelReadTest {

    static String PATH = "D:\\IDEA_Projects\\java-excel-demo\\poi\\src\\main\\java\\com\\stephen\\";

    /**
     * 03版读取
     */
    @Test
    public void testRead03() throws Exception{
        // 1. 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "poi03.xls");
        // 2. 创建工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        // 3. 获取表
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取行
        Row row = sheet.getRow(0);
        // 5. 获取列
        Cell cell = row.getCell(1);
        // 6. 获取列的值
        System.out.println(cell.getStringCellValue());
        // 7. 关闭流
        inputStream.close();
    }

    /**
     * 07版读取
     */
    @Test
    public void testRead07() throws Exception{
        // 1. 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "poi07.xlsx");
        // 2. 创建工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 3. 获取表
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取行
        Row row = sheet.getRow(0);
        // 5. 获取列
        Cell cell = row.getCell(1);
        // 6. 获取列的值
        System.out.println(cell.getStringCellValue());
        // 7. 关闭流
        inputStream.close();
    }

    /**
     * 不同数据类型的读取
     * 数字默认都是取出的double类型,看具体怎么处理
     */
    @Test
    public void testCellType() throws Exception {
        // 1. 获取文件流
        FileInputStream inputStream = new FileInputStream(PATH + "poi03.xls");
        // 2. 创建工作簿
        Workbook workbook = new HSSFWorkbook(inputStream);
        // 3. 获取sheet
        Sheet sheet = workbook.getSheetAt(0);
        // 4. 获取标题内容
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            // 获取总列数
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    // 获取列类型
                    int cellType = cell.getCellType();
                    // 获取列的值
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
            System.out.println();
        }
        // 5. 获取表中内容
        // 获取总行数
        int rouCount = sheet.getPhysicalNumberOfRows();
        // 从第二行开始
        for (int rouNum = 1; rouNum < rouCount; rouNum++) {
            Row rowData = sheet.getRow(rouNum);
            if (rowData != null) {
                // 读取列总数
                int cellCount = rowData.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = rowData.getCell(cellNum);
                    // 匹配列的数据类型
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                // 转成String输出
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字(日期、普通数字)
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                                } else { // 普通数字
                                    // 转成字符串输出
                                    // 防止数字过长,输出会有问题
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                cellValue = cell.getCellFormula();
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 数据类型错误
                                break;
                            default:
                                System.out.println("未知类型");
                                break;
                        }
                        // 输出列的内容
                        System.out.println(cellValue);
                    }
                }
            }
        }
        // 关闭流
        inputStream.close();
    }

}
